package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.Equipment;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.util.ValueUtil;

public class EquipmentDao {
	public List<Equipment> getEquipmentList() {
		List<Equipment> equipmentList = new ArrayList<Equipment>();
		String sql = "select * from equipment";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Equipment equipment = new Equipment();
				equipment.setId(ValueUtil.getLong(rs.getLong("ID")));
				equipment.setEquipName(ValueUtil.getString(rs.getString("EQUIP_NAME")));
				equipment.setDepartmentID(ValueUtil.getLong(rs.getLong("DEPARTMENT_ID")));

				equipmentList.add(equipment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return equipmentList;
	}

	public List<Equipment> getEquipmentListFromDepart2() {
		List<Equipment> equipmentList = new ArrayList<Equipment>();
		String sql = "select d2.DEPARTMENT1_ID, d2.DEPARTMENT2_NAME, d2.EQUIP_ID, d1.DEPARTMENT1_NAME " + "from department2 as d2 "
				+ "left join department1 as d1 on d2.DEPARTMENT1_ID = d1.ID where d2.IS_EQUIP=1";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Equipment equipment = new Equipment();
				equipment.setDepartmentID(rs.getLong("DEPARTMENT1_ID"));
				equipment.setId(ValueUtil.getLong(rs.getLong("EQUIP_ID")));
				equipment.setEquipName(ValueUtil.getString(rs.getString("DEPARTMENT2_NAME")));
				equipment.setDepart1Name(ValueUtil.getString(rs.getString("DEPARTMENT1_NAME")));

				equipmentList.add(equipment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return equipmentList;
	}

	public List<Equipment> getEquipmentList(String nameLike, Long departID, int pageNum, int pageSize) {
		List<Equipment> equipmentList = new ArrayList<Equipment>();
		String sql = null;
		if (departID != null) {
			sql = "select * from (select top " + pageSize + " * from (select top " + pageNum * pageSize
					+ " ID, EQUIP_NAME from equipment where EQUIP_NAME like '%" + nameLike + "%' and DEPARTMENT_ID=? "
					+ "order by ID asc) as A order by ID desc) as B order by ID asc";
		} else {
			sql = "select * from (select top " + pageSize + " * from (select top " + pageNum * pageSize
					+ " ID, EQUIP_NAME from equipment where EQUIP_NAME like '%" + nameLike + "%'"
					+ "order by ID asc) as A order by ID desc) as B order by ID asc";
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			if (departID != null) {
				pstm.setLong(1, departID);
			}

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Equipment equipment = new Equipment();
				equipment.setId(ValueUtil.getLong(rs.getLong("ID")));
				equipment.setEquipName(ValueUtil.getString(rs.getString("EQUIP_NAME")));

				equipmentList.add(equipment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return equipmentList;
	}

	public Equipment getEquipment(Long id) {
		String sql = "select * from equipment where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Equipment equipment = new Equipment();
				equipment.setId(id);
				equipment.setEquipName(ValueUtil.getString(rs.getString("EQUIP_NAME")));
				equipment.setDepartmentID(ValueUtil.getLong(rs.getLong("DEPARTMENT_ID")));

				return equipment;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public void addEquipment(Equipment equipment) {
		String sql = "insert into equipment(EQUIP_NAME, DEPARTMENT_ID) values(?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm = conn.prepareStatement(sql);
			pstm.setString(1, equipment.getEquipName());
			pstm.setLong(2, equipment.getDepartmentID());

			pstm.executeUpdate();

			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public boolean addUserEquipmentMapping(User registerUser, Set<Equipment> equipmentSet) {
		String selectSQL = "select ID from user_equip_mapping where USER_ID=? and CORRESPONDED_EQUIPMENT_ID=?";
		String sql = "insert into user_equip_mapping(USER_ID, CORRESPONDED_EQUIPMENT_ID, IS_EQUIP_MANAGER) " + "values(?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSQL);
			pstm1 = conn.prepareStatement(sql);
			for (Equipment equip : equipmentSet) {
				pstm.setLong(1, registerUser.getId());
				pstm.setLong(2, equip.getId());

				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm1.setLong(1, registerUser.getId());
					pstm1.setLong(2, equip.getId());
					pstm1.setBoolean(3, equip.isEquipmentManager());

					pstm1.executeUpdate();
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean deleteUserMappingEquipments(Long userID) {
		String sql = "delete from user_equip_mapping where USER_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);
			pstm.executeUpdate();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public Set<Equipment> getUserMappingEquipmentList(Long userID, boolean isEquipManager) {
		String sql = "SELECT user_equip_mapping.[ID],[USER_ID],[CORRESPONDED_EQUIPMENT_ID],[IS_EQUIP_MANAGER],equipment.EQUIP_NAME "
				+ "FROM [user_equip_mapping] " + "left outer join equipment on user_equip_mapping.CORRESPONDED_EQUIPMENT_ID=equipment.ID "
				+ "where [USER_ID]=?";
		if (isEquipManager) {
			sql += " and IS_EQUIP_MANAGER=1";
		}

		Set<Equipment> equipList = new HashSet<Equipment>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Equipment equipment = new Equipment();
				equipment.setId(rs.getLong("CORRESPONDED_EQUIPMENT_ID"));
				equipment.setEquipName(rs.getString("EQUIP_NAME"));
				equipment.setEquipmentManager(rs.getBoolean("IS_EQUIP_MANAGER"));

				equipList.add(equipment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return equipList;
	}

	public List<User> getEquipmentMappingUser(Long equipID, int departType) {
		String sql = "select USER_ID, employee.NAME from user_equip_mapping "
				+ "left outer join employee on user_equip_mapping.USER_ID=employee.ID "
				+ "left outer join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "where user_equip_mapping.CORRESPONDED_EQUIPMENT_ID=? and department1.DEPARTMENT_TYPE=?";
		List<User> userList = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, equipID);
			pstm.setInt(2, departType);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("USER_ID"));
				user.setName(rs.getString("NAME"));

				userList.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userList;
	}

	public Equipment getEquipByName(String equipName) {
		String sql = "select ID, EQUIP_NAME, DEPARTMENT_ID from equipment where EQUIP_NAME=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setString(1, equipName);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Equipment equip = new Equipment();
				equip.setId(rs.getLong("ID"));
				equip.setEquipName("EQUIP_NAME");
				equip.setDepartmentID(rs.getLong("DEPARTMENT_ID"));

				return equip;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<User> getEquipmentZhuangzhizhang(Long equipID) {
		String sql = "select department3.ID from department3 inner join department2 on department3.DEPARTMENT2_ID=department2.ID "
				+ "where IS_EQUIP=1 and ROLE_ID=4 and EQUIP_ID=?";
		String getUserSQL = "select ID, NAME from employee where DEPARTMENT3_ID=?";

		List<User> userlist = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(getUserSQL);

			pstm.setLong(1, equipID);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Long depart3ID = rs.getLong("ID");
				pstm1.setLong(1, depart3ID);

				ResultSet rs2 = pstm1.executeQuery();
				while (rs2.next()) {
					User user = new User();
					user.setId(rs2.getLong("ID"));
					user.setName(rs2.getString("NAME"));

					userlist.add(user);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userlist;
	}
	
	public List<User> getEquipmentYixianyuangong(Long equipID) {
		String sql = "select department3.ID from department3 inner join department2 on department3.DEPARTMENT2_ID=department2.ID "
				+ "where IS_EQUIP=1 and ROLE_ID=1 and EQUIP_ID=?";
		String getUserSQL = "select ID, NAME from employee where DEPARTMENT3_ID=?";

		List<User> userlist = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(getUserSQL);

			pstm.setLong(1, equipID);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Long depart3ID = rs.getLong("ID");
				pstm1.setLong(1, depart3ID);

				ResultSet rs2 = pstm1.executeQuery();
				while (rs2.next()) {
					User user = new User();
					user.setId(rs2.getLong("ID"));
					user.setName(rs2.getString("NAME"));

					userlist.add(user);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userlist;
	}
}
